create or replace procedure update_Resident(p_id in patient_t.patient_id%type,b_id in bed_t.bed_id%Type)
IS
begin
update resident_t
set date_leave=sysdate
where patient_id=p_id
and date_leave is null;
if(b_id!='Leave') then
insert into resident_t
values(p_id,b_id,sysdate,null);
end if;
if(b_id='Leave') then
update patient_t
set admission_type='O'
where patient_id=p_id;
end if;
commit;
exception
when others then DBMS_OUTPUT.PUT_LINE(sqlerrm);
end;
/
show errors;


create or replace procedure add_Resident(p_id in patient_t.patient_id%type, b_id in bed_t.bed_id%Type)
IS
begin
insert into resident_t
values(p_id,b_id,sysdate,null);
update patient_t
set admission_type='R'
where patient_id=p_id;
commit;
exception
when others then DBMS_OUTPUT.PUT_LINE(sqlerrm);
end;
/
show errors;

drop sequence drug_order_seq;
create sequence drug_order_seq
minvalue 1
maxvalue 9999999999999
start with 9
increment by 1
cache 20;
create or replace procedure add_drug_order(p_id in patient_t.patient_id%type)
IS
v_drug_id drug_treatment_t.drug_id%type;
v_treatment_id drug_treatment_t.treatment_id%type;
v_quantity drug_treatment_t.quantity%type;
v_order_id drug_order_t.order_id%type;
cursor drug_tre_cur is
SELECT drug_id,quantity,treatment_id FROM drug_treatment_t 
where 
treatment_id=(select treatment_id from treatment_t where patient_id=p_id and
assigned_date=(select max(assigned_date) from treatment_t where patient_id=p_id));
begin
open drug_tre_cur;
Loop
fetch drug_tre_cur into v_drug_id,v_quantity, v_treatment_id;
exit when drug_tre_cur%notfound;
v_order_id:='DO'||LPAD(drug_order_seq.nextval,3,'0');
insert into drug_order_t values(v_order_id,sysdate,v_treatment_id,v_drug_id,v_quantity,p_id);
update drug_t
set quantity=quantity-v_quantity
where drug_id=v_drug_id;
end loop;
close drug_tre_cur;
commit;
exception
when others then DBMS_OUTPUT.PUT_LINE(sqlerrm);
end;
/
show errors;

drop sequence lab_order_seq;
create sequence lab_order_seq
minvalue 1
maxvalue 9999999999999
start with 8
increment by 1
cache 20;

create or replace procedure add_report(p_id in patient_t.patient_id%type, 
oplab_id in operation_lab_treatment_t.op_lab_id%type, 
report in report_t.description%type,
person in person_t.person_id%type)
is 
v_report_id report_t.report_id%type;
v_order_id operation_lab_order_t.order_id%type;
v_treatment_id treatment_t.treatment_id%type;
v_date date;
begin
select treatment_id, assigned_date
into v_treatment_id, v_date
from treatment_t where patient_id=p_id and
assigned_date=(select max(assigned_date) from treatment_t where patient_id=p_id );
v_order_id:='LO'||LPAD(lab_order_seq.nextval,3,'0');
v_report_id:='R'||LPAD(lab_order_seq.currval,4,'0');
insert into operation_lab_order_t values(v_order_id,v_date,v_treatment_id,oplab_id,p_id);
insert into report_t values(v_report_id,person,sysdate,v_treatment_id,oplab_id,report);
commit;
end;
/
show errors;

drop sequence person_seq;
create sequence person_seq
minvalue 1
maxvalue 99999999
start with 26
increment by 1
cache 20;

drop sequence user_seq;
create sequence user_seq
minvalue 1
maxvalue 99999999
start with 26
increment by 1
cache 20;

drop sequence patient_seq;
create sequence patient_seq
minvalue 1
maxvalue 99999999
start with 11
increment by 1
cache 20;

create or replace procedure register(fname in person_t.person_first_name%type,lname in person_t.person_last_name%type, 
street in person_t.person_street%type,city in person_t.person_city%type,state_a in person_t.person_state%type,zip in person_t.person_zip%type,
homephone in person_t.person_home_phone%type,workphone in person_t.person_work_phone%type,
birthday in person_t.person_birthdate%type,email in person_t.person_email%type, marriage in patient_t.marriage%type,
company in patient_t.company%type, policy in patient_t.policy_no%type,group_n in patient_t.group_no%type,companyphone in patient_t.company_phone%type,
sfname in patient_t.sub_fname%type,slname in patient_t.sub_lname%type, relation in patient_t.sub_relationship%type, saddress in patient_t.sub_address%type,sphone in patient_t.sub_phone%type)
IS
v_patient_id patient_t.patient_id%type;
v_user_id user_t.user_id%type;
begin
insert into person_t values
(person_seq.nextval,fname,lname,street,city,state_a,zip,workphone,homephone,birthday,email);
v_patient_id:='P'||LPAD(patient_seq.nextval,4,'0');
insert into patient_t values
(v_patient_id,'O',sysdate,marriage,company,policy,group_n,companyphone,slname,sfname,relation,saddress,sphone,person_seq.currval,null,null);
v_user_id:='U'||LPAD(user_seq.nextval,5,'0');
insert into user_t values(v_user_id,'password','patient',person_seq.currval);
commit;
exception
when others then DBMS_OUTPUT.PUT_LINE(sqlerrm);
end;
/
show errors;

create or replace procedure updatePatient(pid person_t.person_id%type, 
street in person_t.person_street%type,city in person_t.person_city%type,state_a in person_t.person_state%type,zip in person_t.person_zip%type,
homephone in person_t.person_home_phone%type,workphone in person_t.person_work_phone%type,
email in person_t.person_email%type, marriage_p in patient_t.marriage%type,
company_p in patient_t.company%type, policy in patient_t.policy_no%type,group_n in patient_t.group_no%type,companyphone in patient_t.company_phone%type,
sfname in patient_t.sub_fname%type,slname in patient_t.sub_lname%type, relation in patient_t.sub_relationship%type, saddress in patient_t.sub_address%type,sphone in patient_t.sub_phone%type)
IS
begin
update person_t
set person_street=street,person_city=city,person_state=state_a,person_zip=zip,
person_home_phone=homephone,person_work_phone=workphone,
person_email=email
where person_id=pid;
update patient_t
set marriage=marriage_p,
company=company_p,policy_no=policy,group_no=group_n,company_phone=companyphone,
sub_fname=sfname,sub_lname=slname,sub_relationship=relation,sub_address=saddress,sub_phone=sphone
where person_id=pid;
commit;
exception
when others then DBMS_OUTPUT.PUT_LINE(sqlerrm);
end;
/
show errors;

create or replace procedure delete_Patient(p_id patient_t.patient_id%TYPE)
IS
v_person_id person_t.person_id%type;
v_treatment_id treatment_t.treatment_id%type;
cursor treat_cur is
select treatment_id from treatment_t
where patient_id=p_id;
begin
select person_id
into v_person_id
from patient_t
where patient_id=p_id;
delete from Drug_Order_t
where patient_id=p_id;
delete from Operation_Lab_Order_t
where patient_id=p_id;
open treat_cur;
loop
fetch treat_cur into v_treatment_id;
exit when treat_cur%NOTFOUND;
delete from REPORT_t
where treatment_id=v_treatment_id;
delete from OPERATION_LAB_TREATMENT_T
where treatment_id=v_treatment_id;
delete from DRUG_TREATMENT_T
where treatment_id=v_treatment_id;
end loop;
close treat_cur;
delete from TREATMENT_t
where patient_id=p_id;
delete from RESIDENT_t
where patient_id=p_id;
delete from OUTPATIENT_t
where patient_id=p_id;
delete from PATIENT_t
where patient_id=p_id;
delete from USER_t
where person_id=v_person_id;
delete from PERSON_t
where person_id=v_person_id;
end;
/
show errors
